<?php
class ModelSaleOrder extends Model {
	public function getOrder($order_id) {
		$order_query = $this->db->query("SELECT *, (SELECT c.fullname FROM " . DB_PREFIX . "customer c WHERE c.customer_id = o.customer_id) AS customer , (SELECT c.recommended_code FROM " . DB_PREFIX . "customer c where c.customer_id = o.customer_id) AS recommended_code FROM `" . DB_PREFIX . "order` o WHERE o.order_id = '" . (int)$order_id . "'");

		if ($order_query->num_rows) {
			$reward = 0;

			$order_product_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");

			foreach ($order_product_query->rows as $product) {
				$reward += $product['reward'];
			}

			$country_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "country` WHERE country_id = '" . (int)$order_query->row['payment_country_id'] . "'");

			if ($country_query->num_rows) {
				$payment_iso_code_2 = $country_query->row['iso_code_2'];
				$payment_iso_code_3 = $country_query->row['iso_code_3'];
			} else {
				$payment_iso_code_2 = '';
				$payment_iso_code_3 = '';
			}

			$zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE zone_id = '" . (int)$order_query->row['payment_zone_id'] . "'");

			if ($zone_query->num_rows) {
				$payment_zone_code = $zone_query->row['code'];
			} else {
				$payment_zone_code = '';
			}

			$country_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "country` WHERE country_id = '" . (int)$order_query->row['shipping_country_id'] . "'");

			if ($country_query->num_rows) {
				$shipping_iso_code_2 = $country_query->row['iso_code_2'];
				$shipping_iso_code_3 = $country_query->row['iso_code_3'];
			} else {
				$shipping_iso_code_2 = '';
				$shipping_iso_code_3 = '';
			}

			$zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE zone_id = '" . (int)$order_query->row['shipping_zone_id'] . "'");

			if ($zone_query->num_rows) {
				$shipping_zone_code = $zone_query->row['code'];
			} else {
				$shipping_zone_code = '';
			}

			if ($order_query->row['affiliate_id']) {
				$affiliate_id = $order_query->row['affiliate_id'];
			} else {
				$affiliate_id = 0;
			}

			$this->load->model('marketing/affiliate');

			$affiliate_info = $this->model_marketing_affiliate->getAffiliate($affiliate_id);

			if ($affiliate_info) {
				$affiliate_fullname = $affiliate_info['fullname'];
			} else {
				$affiliate_fullname = '';
			}

			$this->load->model('localisation/language');

			$language_info = $this->model_localisation_language->getLanguage($order_query->row['language_id']);

			if ($language_info) {
				$language_code = $language_info['code'];
				$language_directory = $language_info['directory'];
			} else {
				$language_code = '';
				$language_directory = '';
			}

			return array(
                'user_id'                 => $order_query->row['recommend_usr_id'],/* @author sonicsjh */
				'order_id'                => $order_query->row['order_id'],
				'is_pay'				  => $order_query->row['is_pay'],
				'invoice_no'              => $order_query->row['invoice_no'],
				'invoice_prefix'          => $order_query->row['invoice_prefix'],
				'store_id'                => $order_query->row['store_id'],
				'store_name'              => $order_query->row['store_name'],
				'store_url'               => $order_query->row['store_url'],
				'customer_id'             => $order_query->row['customer_id'],
				'customer'                => $order_query->row['customer'],
				'customer_group_id'       => $order_query->row['customer_group_id'],
				'recommended_code'		  => $order_query->row['recommended_code'],
				'fullname'               => $order_query->row['fullname'],
				'email'                   => $order_query->row['email'],
				'telephone'               => $order_query->row['telephone'],
				'fax'                     => $order_query->row['fax'],
				'custom_field'            => json_decode($order_query->row['custom_field'], true),
				'payment_fullname'       => $order_query->row['payment_fullname'],
				'payment_company'         => $order_query->row['payment_company'],
				'payment_address'       => $order_query->row['payment_address'],
				'payment_postcode'        => $order_query->row['payment_postcode'],
				'payment_city'            => $order_query->row['payment_city'],
				'payment_city_id'            => $order_query->row['payment_city_id'],
				'payment_zone_id'         => $order_query->row['payment_zone_id'],
				'payment_zone'            => $order_query->row['payment_zone'],
				'payment_zone_code'       => $payment_zone_code,
				'payment_country_id'      => $order_query->row['payment_country_id'],
				'payment_country'         => $order_query->row['payment_country'],
				'payment_iso_code_2'      => $payment_iso_code_2,
				'payment_iso_code_3'      => $payment_iso_code_3,
				'payment_address_format'  => $order_query->row['payment_address_format'],
				'payment_custom_field'    => json_decode($order_query->row['payment_custom_field'], true),
				'payment_method'          => $order_query->row['payment_method'],
				'payment_code'            => $order_query->row['payment_code'],
				'shipping_fullname'      => $order_query->row['shipping_fullname'],
				'shipping_company'        => $order_query->row['shipping_company'],
				'shipping_address'      => $order_query->row['shipping_address'],
				'shipping_postcode'       => $order_query->row['shipping_postcode'],
				'shipping_city'           => $order_query->row['shipping_city'],
				'shipping_city_id'           => $order_query->row['shipping_city_id'],
				'shipping_zone_id'        => $order_query->row['shipping_zone_id'],
				'shipping_zone'           => $order_query->row['shipping_zone'],
				'shipping_zone_code'      => $shipping_zone_code,
				'shipping_country_id'     => $order_query->row['shipping_country_id'],
				'shipping_country'        => $order_query->row['shipping_country'],
				'shipping_iso_code_2'     => $shipping_iso_code_2,
				'shipping_iso_code_3'     => $shipping_iso_code_3,
				'shipping_address_format' => $order_query->row['shipping_address_format'],
				'shipping_custom_field'   => json_decode($order_query->row['shipping_custom_field'], true),
				'shipping_method'         => $order_query->row['shipping_method'],
				'shipping_code'           => $order_query->row['shipping_code'],
				'shipping_telephone'      => $order_query->row['shipping_telephone'],
				'comment'                 => $order_query->row['comment'],
				'total'                   => $order_query->row['total'],
				'ori_total'               => $order_query->row['ori_total'],
				'reward'                  => $reward,
				'order_status_id'         => $order_query->row['order_status_id'],
				'affiliate_id'            => $order_query->row['affiliate_id'],
				'affiliate_fullname'     => $affiliate_fullname,
				'commission'              => $order_query->row['commission'],
				'language_id'             => $order_query->row['language_id'],
				'language_code'           => $language_code,
				'language_directory'      => $language_directory,
				'currency_id'             => $order_query->row['currency_id'],
				'currency_code'           => $order_query->row['currency_code'],
				'currency_value'          => $order_query->row['currency_value'],
				'ip'                      => $order_query->row['ip'],
				'forwarded_ip'            => $order_query->row['forwarded_ip'],
				'user_agent'              => $order_query->row['user_agent'],
				'accept_language'         => $order_query->row['accept_language'],
				'date_added'              => $order_query->row['date_added'],
				'date_modified'           => $order_query->row['date_modified'],
				'bill_status'             => $order_query->row['bill_status'],
				'parent_id'             => $order_query->row['parent_id'],
				'sign_price'             => $order_query->row['sign_price'],
				'verify_status'             => $order_query->row['verify_status'],
				'conform_price'             => $order_query->row['conform_price'],
				'conform_pay_status'             => $order_query->row['conform_pay_status'],
				'remarks'             => $order_query->row['remarks'],
			);
		} else {
			return;
		}
	}

    //用于页面按钮：导出订单
	public function getExportOrders($data = array()){
		$map = array();
		if (!empty($data['filter_order_id'])) {
			$map['o.order_id'] = (int)$data['filter_order_id'];
		}

		if (!empty($data['filter_order_status'])) {
			$map['o.order_status_id'] = (int)$data['filter_order_status'];
		}else{
			$map['o.order_status_id'] = array('neq','16');
		}

		if (!empty($data['filter_verify_status']) || $data['filter_verify_status'] == '0') {
			$map['o.verify_status'] = $data['filter_verify_status'];
		}

		if (!empty($data['filter_recommended_code'])) {
			$map['c.recommended_code'] = array('like','%'.$data['filter_recommended_code'].'%');
		}

		if (!empty($data['filter_customer'])) {
			$map['o.fullname'] = array('like',"%".$data['filter_customer']."%");
		}

		if (isset($data['filter_is_pay'])) {
			if($data['filter_is_pay']!=3){
				$map['o.is_pay'] = array('like','%'.$data['filter_is_pay'].'%');
			}
		}
		// var_dump($data['filter_is_shelf_order']);die();
		if (isset($data['filter_is_shelf_order'])) {
			if($data['filter_is_shelf_order']==0){
				$map['o.shelf_order_id'] =array('eq',0);
				// $sql .= " AND o.shelf_order_id =0";
			}elseif ($data['filter_is_shelf_order']==1) {
				$map['o.shelf_order_id'] =array('neq',0);

				// $sql .= " AND o.shelf_order_id <>0";
			}
		}

		if (!empty($data['filter_date_added'])) {
			$added_start = $data['filter_date_added'];
			$added_end = $data['filter_date_added']." 23:59:59";
			$map['o.date_added'] = array('between',array($added_start,$added_end));
			//$sql .= " AND DATE(o.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
		}

		if ((!empty($data['filter_date_start']))&&(!empty($data['filter_date_end']))) {
			$filter_date_end = $data['filter_date_end']." 23:59:59";
			$map['o.date_added'] = array('between',array($data['filter_date_start'],$filter_date_end));
			// $sql .= " AND DATE(o.date_added) >= DATE('" . $this->db->escape($data['filter_date_start']) . "')";
		}else{

			if (!empty($data['filter_date_end'])) {
				$filter_date_end2 = $data['filter_date_end']." 23:59:59";
				$map['o.date_added'] = array('elt',$filter_date_end2);
			// $sql .= " AND DATE(o.date_added) <= DATE('" . $this->db->escape($data['filter_date_end']) . "')";
			}

			if (!empty($data['filter_date_start'])) {
				$map['o.date_added'] = array('egt',$data['filter_date_start']);
			// $sql .= " AND DATE(o.date_added) >= DATE('" . $this->db->escape($data['filter_date_start']) . "')";
			}
		}

		if (!empty($data['filter_date_modified'])) {
			$map['o.date_modified'] = $this->db->escape($data['filter_date_modified']);
			// $sql .= " AND DATE(o.date_modified) = DATE('" . $this->db->escape($data['filter_date_modified']) . "')";
		}

		if (!empty($data['filter_total'])) {
			$map['o.total'] = (float)$data['filter_total'];
			// $sql .= " AND o.total = '" . (float)$data['filter_total'] . "'";
		}

		if (!empty($data['filter_logcenter'])) {
			$map['o.logcenter_id'] = (int)$data['filter_logcenter'];
			// $sql .= " AND o.logcenter_id = '" . (int)$data['filter_logcenter'] . "'";
		}

		if (!empty($data['filter_user_id'])) {
			$map['c.user_id'] = (int)$data['filter_user_id'];
		}

        if (!empty($data['filter_product_code'])) {
            $data['filter_product_code'] = str_pad(intval($data['filter_product_code']), 11, 0);
            $map['_string'] = " (EXISTS (SELECT `product_code` FROM `order_product` AS OP WHERE OP.`product_code`='".$data['filter_product_code']."' AND OP.`order_id`=o.`order_id`) OR EXISTS (SELECT `product_code` FROM `order_product` AS OP WHERE OP.`order_id`=o.`order_id` AND (SELECT `order_product_id` FROM `order_product_group` AS OPG WHERE OPG.`product_code`='".$data['filter_product_code']."' AND OPG.`order_product_id`=OP.`order_product_id`)))";
        }

		$sort_data = array(
			'o.order_id',
			'customer',
			'status',
			'o.date_added',
			'o.date_modified',
			'o.total'
		);

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			// $sql .= " ORDER BY " . $data['sort'];
			$order = $data['sort'];
		} else {
			// $sql .= " ORDER BY o.order_id";
			$order = "o.order_id";
		}

		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$order .= " DESC";
		} else {
			$order .= " ASC";
		}

		$model = M('order_product');
		$order_data = $model
		->alias('op')
		->join('LEFT JOIN `order` o ON op.order_id = o.order_id')
		->join('LEFT JOIN `user` u ON u.user_id=o.recommend_usr_id')
		->join('LEFT JOIN `vendor` v ON v.vproduct_id=op.product_id')
		->join('LEFT JOIN `vendors` vs ON vs.vendor_id=v.vendor ')
		->join('LEFT JOIN `customer` c ON c.customer_id=o.customer_id')
		->join('LEFT JOIN `product` p ON p.product_id=op.product_id')
		->join('LEFT join `product_to_category` ptc ON ptc.product_id=p.product_id')
		->join('LEFT join `category` cate ON cate.category_id=ptc.category_id')
		->join('LEFT join `category_description` cd ON cd.category_id=ptc.category_id')
		->group('p.product_id,op.order_id')
		->where($map)
		->order($order,'cate.category_id asc')
		->field("c.recommended_code,o.bill_status, o.order_id,o.shipping_country,o.shipping_zone, c.company_name AS customer, (SELECT os.name from order_status os WHERE os.order_status_id = o.order_status_id AND os.language_id = '" . (int)$this->config->get('config_language_id') . "') AS status, o.shipping_code, o.currency_code, o.currency_value, o.date_added, o.date_modified,vs.vendor_name,op.name as product_name,p.product_code,p.price as 'pprice',p.product_type,p.sku,op.order_product_id,op.model,op.quantity,op.lack_quantity,op.price,op.total,o.shipping_address,c.telephone,cd.name as cd_name,op.ship_status,o.is_pay,o.payment_method,o.payment_company,o.paid_time,o.auditted_time,o.delivered_time,o.completed_time,o.out_time,v.product_cost,p.product_class1,p.product_class2,p.product_class3,u.username as u_name,u.fullname as u_fullname")
		->select();
        // echo $model->getLastSql();
        // die();
		return $order_data;
	}

	public function getExportCombineProduct($order_product_id){
		$sql = "SELECT opg.total,vs.vendor_name AS vsname,pd.name AS pdname,p.product_code,p.sku,p.model,opg.quantity,opg.lack_quantity,opg.price,cd.name AS cdname,v.product_cost,p.price as 'pprice',p.product_class1,p.product_class2,p.product_class3 FROM `".DB_PREFIX."order_product_group` opg LEFT JOIN `".DB_PREFIX."product` p ON opg.product_id = p.product_id LEFT JOIN `".DB_PREFIX."product_description` pd ON p.product_id = pd.product_id LEFT JOIN `".DB_PREFIX."vendor` v ON p.product_id = v.vproduct_id LEFT JOIN `".DB_PREFIX."vendors` vs ON vs.vendor_id = v.vendor LEFT JOIN `".DB_PREFIX."product_to_category` ptc ON p.product_id = ptc.product_id LEFT JOIN `".DB_PREFIX."category` cate ON cate.category_id=ptc.category_id LEFT JOIN `".DB_PREFIX."category_description` cd ON cd.category_id=ptc.category_id WHERE opg.order_product_id = '".$order_product_id."' GROUP BY opg.order_product_id,opg.product_id ORDER BY cate.category_id ASC";

		$query = $this->db->query($sql);

		return $query->rows;
	}

    //很多地方调用，包括（页面按钮：导出汇总订单）
	public function getOrders($data = array()) {
		$data = $this->trim_all_arr($data);
		$sql = "SELECT u.fullname as recommended_name,o.order_status_id,o.difference,o.payment_method,o.is_pay,c.recommended_code,o.comment,o.sign_price,o.bill_status, o.order_id,o.shipping_country,o.shipping_zone,o.shop_type,o.shipping_address,log.logcenter_name, o.shipping_company,o.fullname AS customer, (SELECT os.name FROM " . DB_PREFIX . "order_status os WHERE os.order_status_id = o.order_status_id AND os.language_id = '" . (int)$this->config->get('config_language_id') . "') AS status, o.shipping_code, o.total, o.ori_total, o.currency_code, o.currency_value, o.date_added, o.date_modified, o.paid_time, o.auditted_time, o.delivered_time, o.completed_time, o.out_time, o.hang_status, ot.value, o.verify_status,o.shelf_order_id, o.total_percant, o.first_stock_out_rate, o.final_stock_out_rate FROM `" . DB_PREFIX . "order` o LEFT JOIN `" . DB_PREFIX . "customer` c ON c.customer_id=o.customer_id LEFT JOIN `" . DB_PREFIX . "user` u ON u.user_id=o.recommend_usr_id LEFT JOIN `" . DB_PREFIX . "logcenters` log ON log.logcenter_id=o.logcenter_id LEFT JOIN `".DB_PREFIX."order_total` ot ON o.order_id = ot.order_id";

        if (isset($data['filter_order_status'])) {
			$implode = array();

			$order_statuses = explode(',', $data['filter_order_status']);

			foreach ($order_statuses as $order_status_id) {
				$implode[] = "o.order_status_id = '" . (int)$order_status_id . "'";
			}

			if ($implode) {
				$sql .= " WHERE (" . implode(" OR ", $implode) . ")";
			}
		} else {
			$sql .= " WHERE o.order_status_id != '16'";
		}

		if (!empty($data['filter_verify_status']) || $data['filter_verify_status'] == '0') {
			$sql .= " AND verify_status = '" . (int)$data['filter_verify_status'] . "'";
		}

		if (!empty($data['filter_order_id'])) {
			$sql .= " AND o.order_id = '" . (int)$data['filter_order_id'] . "'";
		}

		if (isset($data['filter_is_pay'])) {
			if($data['filter_is_pay']!=3){
				$sql .= " AND o.is_pay like '%" . (int)$data['filter_is_pay'] . "%'";
			}
		}
		if (isset($data['filter_is_shelf_order'])) {
			if($data['filter_is_shelf_order']==0){
				$sql .= " AND o.shelf_order_id =0";
			}elseif ($data['filter_is_shelf_order']==1) {
				$sql .= " AND shelf_order_id <> 0";
			}
		}
		$user_id = $this->user->getId();
		$user_group_id = $this->user->getGroupId();
		$sales_group_id = $this->config->get('config_sales_user');
		if($user_group_id == $sales_group_id){
			$sql .= "AND c.user_id ='".(int)$user_id."'";
		}

        /*
         * 强制筛选分销商名下订单，用于 controller/sale/order_distributor
         * @author sonicsjh
         */
        if ((int)$data['filter_user_id'] > 0) {
            $sql .= " AND c.user_id ='".$data['filter_user_id']."'";
        }

		if (!empty($data['filter_recommended_code'])) {
			$sql .= " AND c.recommended_code like '%" . (string)$data['filter_recommended_code'] . "%'";
		}

		if (!empty($data['filter_customer'])) {
			$sql .= " AND o.fullname LIKE '%" . $this->db->escape($data['filter_customer']) . "%'";
		}

		if (!empty($data['filter_date_added'])) {
			$sql .= " AND DATE(o.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
		}

		if (!empty($data['filter_date_start'])) {
			$sql .= " AND DATE(o.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$sql .= " AND DATE(o.date_added) < '" . $this->db->escape($data['filter_date_end']) . " 23:59:59'";
		}

		if (!empty($data['filter_date_modified'])) {
			$sql .= " AND DATE(o.date_modified) = DATE('" . $this->db->escape($data['filter_date_modified']) . "')";
		}

		if (!empty($data['filter_total'])) {
			$sql .= " AND o.total = '" . (float)$data['filter_total'] . "'";
		}

		if (!empty($data['filter_logcenter'])) {
			$sql .= " AND o.logcenter_id = '" . (int)$data['filter_logcenter'] . "'";
		}

		$sql .= " AND ot.code = 'sub_total'";

        if (!empty($data['filter_product_code'])) {
            $data['filter_product_code'] = str_pad(intval($data['filter_product_code']), 11, 0);
            $sql .= " AND (EXISTS (SELECT `product_code` FROM `order_product` AS OP WHERE OP.`product_code`='".$data['filter_product_code']."' AND OP.`order_id`=o.`order_id`) OR EXISTS (SELECT `product_code` FROM `order_product` AS OP WHERE OP.`order_id`=o.`order_id` AND (SELECT `order_product_id` FROM `order_product_group` AS OPG WHERE OPG.`product_code`='".$data['filter_product_code']."' AND OPG.`order_product_id`=OP.`order_product_id`)))";
        }

		$sort_data = array(
			'o.order_id',
			'customer',
			'status',
			'verify_status',
			'o.date_added',
			'o.date_modified',
			'o.total',
			'o.ori_total',
			'o.stock_out_rate_by_type',
			'o.stock_out_rate_by_qty',
			'o.stock_out_rate_by_price',
			'o.delivery_rate_by_type',
			'o.delivery_rate_by_qty',
			'o.delivery_rate_by_price',
		);
		$sql .=" group BY o.order_id ";

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];
		} else {
			$sql .= " ORDER BY o.order_id";
		}

		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}
		// echo $sql;die();
		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function getOrderProducts($order_id) {
		$query = $this->db->query("SELECT op.*,(SELECT sku FROM `".DB_PREFIX."product` p WHERE p.product_id=op.product_id) as sku,(SELECT product_type FROM `".DB_PREFIX."product` p2 WHERE p2.product_id=op.product_id) as product_type,i.sumquan AS sumquan FROM `" . DB_PREFIX . "order_product` op LEFT JOIN (SELECT SUM(available_quantity) AS sumquan,product_code FROM `".DB_PREFIX."inventory` WHERE warehouse_id = 4 OR warehouse_id = 10 GROUP BY product_code) i ON i.product_code = op.product_code WHERE op.order_id = '" . (int)$order_id . "' ORDER BY order_ids");

		return $query->rows;
	}

	public function getchildorderproducts($order_product_id){
		$sql = "SELECT opg.*,pd.name AS pdname,p.sku AS sku,p.model AS model,i.sumquan AS sumquan FROM `".DB_PREFIX."order_product_group` opg LEFT JOIN `".DB_PREFIX."product_description` pd ON opg.product_id = pd.product_id LEFT JOIN `".DB_PREFIX."product` p ON opg.product_id = p.product_id LEFT JOIN (SELECT SUM(available_quantity) AS sumquan,product_code FROM `".DB_PREFIX."inventory` WHERE warehouse_id = 4 OR warehouse_id = 10 GROUP BY product_code) i ON i.product_code = opg.product_code WHERE order_product_id = '".$order_product_id."' ORDER BY order_ids";
		$query = $this->db->query($sql);
		// echo $sql;

		return $query->rows;
	}

	public function setOrderProductLackQuantityZero($order_product_id){
		$query = $this->db->query("UPDATE `".DB_PREFIX."order_product` SET lack_quantity = 0 WHERE order_product_id = '".$order_product_id."'");
	}

	public function getOrderProductsWithSku($order_id) {
		$op = M('order_product');
		$where['order_id'] = (int)$order_id;
		$opdata = $op
		->alias('op')
		->join('LEFT JOIN product p ON p.product_id = op.product_id')
		->where($where)
		->select();
		return $opdata;
		// $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");
		// return $query->rows;
	}

	public function getOrderOptions($order_id, $order_product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "' AND order_product_id = '" . (int)$order_product_id . "'");

		return $query->rows;
	}

	public function getOrderVouchers($order_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_voucher WHERE order_id = '" . (int)$order_id . "'");

		return $query->rows;
	}

	public function getOrderVoucherByVoucherId($voucher_id) {
		$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_voucher` WHERE voucher_id = '" . (int)$voucher_id . "'");

		return $query->row;
	}

	public function getOrderTotals($order_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_total WHERE order_id = '" . (int)$order_id . "' ORDER BY sort_order");

		return $query->rows;
	}

	public function getTotalOrders($data = array()) {
		$data = $this->trim_all_arr($data);
		$sql = "SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` o LEFT JOIN `" . DB_PREFIX . "customer` c ON c.customer_id=o.customer_id ";

		if (isset($data['filter_order_status'])) {
			$implode = array();

			$order_statuses = explode(',', $data['filter_order_status']);

			foreach ($order_statuses as $order_status_id) {
				$implode[] = "order_status_id = '" . (int)$order_status_id . "'";
			}

			if ($implode) {
				$sql .= " WHERE (" . implode(" OR ", $implode) . ")";
			}
		} else {
			$sql .= " WHERE order_status_id != '16'";
		}

		if (!empty($data['filter_verify_status']) || $data['filter_verify_status'] == '0') {
			$sql .= " AND verify_status = '" . (int)$data['filter_verify_status'] . "'";
		}

		if (!empty($data['filter_order_id'])) {
			$sql .= " AND order_id = '" . (int)$data['filter_order_id'] . "'";
		}

		if (isset($data['filter_is_pay'])) {
			if($data['filter_is_pay']!=3){
				$sql .= " AND o.is_pay like '%" . (int)$data['filter_is_pay'] . "%'";
			}
		}
		if (isset($data['filter_is_shelf_order'])) {
			if($data['filter_is_shelf_order']==0){
				$sql .= " AND o.shelf_order_id =0";
			}elseif ($data['filter_is_shelf_order']==1) {
				$sql .= " AND o.shelf_order_id <>0";
			}
		}

		$user_id = $this->user->getId();
		$user_group_id = $this->user->getGroupId();
		$sales_group_id = $this->config->get('config_sales_user');
		if($user_group_id == $sales_group_id){
			$sql .= "AND c.user_id ='".(int)$user_id."'";
		}

        /*
         * 强制筛选分销商名下订单，用于 controller/sale/order_distributor
         * @author sonicsjh
         */
        if ((int)$data['filter_user_id'] > 0) {
            $sql .= " AND c.user_id ='".$data['filter_user_id']."'";
        }

		if (!empty($data['filter_customer'])) {
			$sql .= " AND o.fullname LIKE '%" . $this->db->escape($data['filter_customer']) . "%'";
		}

		if (!empty($data['filter_date_added'])) {
			$sql .= " AND DATE(o.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
		}

		if (!empty($data['filter_date_start'])) {
			$sql .= " AND DATE(o.date_added) >= DATE('" . $this->db->escape($data['filter_date_start']) . "')";
		}

		if (!empty($data['filter_date_end'])) {
			$sql .= " AND DATE(o.date_added) <= DATE('" . $this->db->escape($data['filter_date_end']) . "')";
		}

		if (!empty($data['filter_total'])) {
			$sql .= " AND o.total = '" . (float)$data['filter_total'] . "'";
		}

		if (!empty($data['filter_logcenter'])) {
			$sql .= " AND o.logcenter_id = '" . (int)$data['filter_logcenter'] . "'";
		}

		if (!empty($data['filter_recommended_code'])) {
			$sql .= " AND c.recommended_code like '%" . $data['filter_recommended_code'] . "%'";
		}

        if (!empty($data['filter_product_code'])) {
            $data['filter_product_code'] = str_pad(intval($data['filter_product_code']), 11, 0);
            $sql .= " AND (EXISTS (SELECT `product_code` FROM `order_product` AS OP WHERE OP.`product_code`='".$data['filter_product_code']."' AND OP.`order_id`=o.`order_id`) OR EXISTS (SELECT `product_code` FROM `order_product` AS OP WHERE OP.`order_id`=o.`order_id` AND (SELECT `order_product_id` FROM `order_product_group` AS OPG WHERE OPG.`product_code`='".$data['filter_product_code']."' AND OPG.`order_product_id`=OP.`order_product_id`)))";
        }

		$query = $this->db->query($sql);

		return $query->row['total'];
	}

	public function getTotalOrdersByStoreId($store_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE store_id = '" . (int)$store_id . "'");

		return $query->row['total'];
	}

	public function getTotalOrdersByOrderStatusId($order_status_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id = '" . (int)$order_status_id . "' AND order_status_id > '0'");

		return $query->row['total'];
	}

	public function getTotalOrdersByProcessingStatus() {
		$implode = array();

		$order_statuses = $this->config->get('config_processing_status');

		foreach ($order_statuses as $order_status_id) {
			$implode[] = "order_status_id = '" . (int)$order_status_id . "'";
		}

		if ($implode) {
			$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE " . implode(" OR ", $implode));

			return $query->row['total'];
		} else {
			return 0;
		}
	}

	public function getTotalOrdersByCompleteStatus() {
		$implode = array();

		$order_statuses = $this->config->get('config_complete_status');

		foreach ($order_statuses as $order_status_id) {
			$implode[] = "order_status_id = '" . (int)$order_status_id . "'";
		}

		if ($implode) {
			$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE " . implode(" OR ", $implode) . "");

			return $query->row['total'];
		} else {
			return 0;
		}
	}

	public function getTotalOrdersByLanguageId($language_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE language_id = '" . (int)$language_id . "' AND order_status_id > '0'");

		return $query->row['total'];
	}

	public function getTotalOrdersByCurrencyId($currency_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE currency_id = '" . (int)$currency_id . "' AND order_status_id > '0'");

		return $query->row['total'];
	}

	public function createInvoiceNo($order_id) {
		$order_info = $this->getOrder($order_id);

		if ($order_info && !$order_info['invoice_no']) {
			$query = $this->db->query("SELECT MAX(invoice_no) AS invoice_no FROM `" . DB_PREFIX . "order` WHERE invoice_prefix = '" . $this->db->escape($order_info['invoice_prefix']) . "'");

			if ($query->row['invoice_no']) {
				$invoice_no = $query->row['invoice_no'] + 1;
			} else {
				$invoice_no = 1;
			}

			$this->db->query("UPDATE `" . DB_PREFIX . "order` SET invoice_no = '" . (int)$invoice_no . "', invoice_prefix = '" . $this->db->escape($order_info['invoice_prefix']) . "' WHERE order_id = '" . (int)$order_id . "'");

			return $order_info['invoice_prefix'] . $invoice_no;
		}
	}

	public function getOrderHistories($order_id, $start = 0, $limit = 10) {
		if ($start < 0) {
			$start = 0;
		}

		if ($limit < 1) {
			$limit = 10;
		}

		$query = $this->db->query("SELECT oh.date_added, os.name AS status, oh.comment, oh.notify, oh.vendor_id FROM " . DB_PREFIX . "order_history oh LEFT JOIN " . DB_PREFIX . "order_status os ON oh.order_status_id = os.order_status_id WHERE oh.order_id = '" . (int)$order_id . "' AND os.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY oh.date_added ASC LIMIT " . (int)$start . "," . (int)$limit);

		return $query->rows;
	}

	public function getTotalOrderHistories($order_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "order_history WHERE order_id = '" . (int)$order_id . "'");

		return $query->row['total'];
	}

	public function getTotalOrderHistoriesByOrderStatusId($order_status_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "order_history WHERE order_status_id = '" . (int)$order_status_id . "'");

		return $query->row['total'];
	}

	public function getEmailsByProductsOrdered($products, $start, $end) {
		$implode = array();

		foreach ($products as $product_id) {
			$implode[] = "op.product_id = '" . (int)$product_id . "'";
		}

		$query = $this->db->query("SELECT DISTINCT email FROM `" . DB_PREFIX . "order` o LEFT JOIN " . DB_PREFIX . "order_product op ON (o.order_id = op.order_id) WHERE (" . implode(" OR ", $implode) . ") AND o.order_status_id <> '0' LIMIT " . (int)$start . "," . (int)$end);

		return $query->rows;
	}

	public function getAllVendorOrderStatus($order_id) {
		$getOrderStatusID = $this->db->query("SELECT order_status_id FROM  `" . DB_PREFIX . "order` WHERE order_id = '" . (int)$order_id . "'");
		$count1 = $this->db->query("SELECT COUNT(*) as total FROM  `" . DB_PREFIX . "order_status_vendor_update` WHERE order_id = '" . (int)$order_id . "'");
		$count2 = $this->db->query("SELECT COUNT(*) as total FROM  `" . DB_PREFIX . "order_status_vendor_update` WHERE order_id = '" . (int)$order_id . "' AND order_status_id = '" . (int)$getOrderStatusID->row['order_status_id'] . "'");

		if ($count1->row['total'] != $count2->row['total']) {
			$query = $this->db->query("SELECT DISTINCT(`vendor_id`), order_status_id FROM  `" . DB_PREFIX . "order_status_vendor_update` WHERE order_id = '" . (int)$order_id . "'");
			$status_text = '';

			if ($query->rows) {
				foreach ($query->rows as $result) {
					$name = $this->db->query("SELECT vendor_name FROM  `" . DB_PREFIX . "vendors` WHERE vendor_id = '" . (int)$result['vendor_id'] . "'");
					$status = $this->db->query("SELECT name FROM  `" . DB_PREFIX . "order_status` WHERE order_status_id = '" . (int)$result['order_status_id'] . "'");

					if ($name->row) {
						$vname = $name->row['vendor_name'];
					} else {
						$vname = '';
					}

					if ($vname) {
						if (empty($status_text)) {
							$status_text = '<small><b>' . $vname . '</b> - ' . $status->row['name'] . '</small>';
						} else {
							$status_text .= '<br /><small><b>' . $vname . '</b> - ' . $status->row['name'] . '</small>';
						}
					}
				}
				return $status_text;
			} else {
				return false;
			}
		} else {
			$current_status = $this->db->query("SELECT name FROM  `" . DB_PREFIX . "order_status` WHERE order_status_id = '" . (int)$getOrderStatusID->row['order_status_id'] . "'");
			return $current_status->row['name'];
		}
	}

	public function getTotalEmailsByProductsOrdered($products) {
		$implode = array();

		foreach ($products as $product_id) {
			$implode[] = "op.product_id = '" . (int)$product_id . "'";
		}

		$query = $this->db->query("SELECT DISTINCT email FROM `" . DB_PREFIX . "order` o LEFT JOIN " . DB_PREFIX . "order_product op ON (o.order_id = op.order_id) WHERE (" . implode(" OR ", $implode) . ") AND o.order_status_id <> '0'");

		return $query->row['total'];
	}
	public function saveIs_invoice($order_id,$bill_status){

		$order = M('order');
		$data['bill_status'] = $bill_status;
		$where['order_id'] = $order_id;
		$query = $order
		->where($where)
		->data($data)
		->save();
		return $query;
	}

	private function getOrdersByCustomerId($customer_id){
		if(!($customer_id)){
			$customer_id = 0;
		}
		$order = M('order');
		$where['customer_id'] =$customer_id;
		$query = $order
		->where($where)
		->field('SUM(total) as total,count(*) as count')
		->where("date_format(date_added,'%Y-%m') = date_format(now(),'%Y-%m')")
		->where("order_status_id in (" .implode(',', array_merge($this->config->get('config_complete_status'), $this->config->get('config_processing_status'))). ")")
		->select();
		if($query['0']['count']){
			$count = $query['0']['count'];
		}else{
			$count = 0;
		}
		if($query['0']['total']){
			$total = $query['0']['total'];
		}else{
			$total = 0.0000;
		}
		$data = array(
			'count' => $count,
			'total' => $total,
			);
		return $data;
	}

	public function getOrderProductsDetial($order_id){
		$op = M('order_product');
		if(!($order_id)){
			$order_id = 0;
		}
		$where['op.order_id'] = $order_id;
		$query = $op
		->alias('op')
		->join("LEFT JOIN `product` p ON op.product_id = p.product_id ")
		->where($where)
		->field('op.name,op.price,op.quantity,p.sku,op.total,p.product_type,op.order_product_id,op.order_ids')
		->order('op.vendor_id')
		->select();

		return $query;
	}

	public function getSc($order_id){
    $order = M('order');
    if(!($order_id)){
      $where['order_id'] = 0 ;
    }

    $query = $this->getOrder($order_id);

    $this->load->model('customer/customer');
    $reward = $this->model_customer_customer->getMreward($query['customer_id']);
    $money = $this->getOrdersByCustomerId($query['customer_id']);
    $customer_info = $this->model_customer_customer->getCustomer($query['customer_id']);
    $data = array();
    $data = array(
      'order_id'      => $query['order_id'],
      'date_added'    => $query['date_added'],
      'fullname'      => $query['fullname'],
      'customer_id'   => $query['customer_id'],
      'shipping_fullname' => $query['shipping_fullname'],
      'telephone'     => $query['telephone'],
      'payment_method' => $query['payment_method'],
      'is_pay' => $query['is_pay'],
      'Mreward'         => $reward['Mreward'],
      'Areward'     => $reward['Areward'],
      'Mtotal_order'    => $money['count'],
      'Mtotal_money'    => $money['total'],
      'shipping_address'    => $query['shipping_country'].$query['shipping_zone'].$query['shipping_city'].$query['shipping_address'],
      'total' => $query['total'],
      'company_name' => $customer_info['company_name'],

      );
    $total_item_number = 0;
    $products = $this->getOrderProductsDetial($order_id);
    if($products){
      foreach ($products as $result) {
      	if($result['product_type'] != 2){
	        $data['products'][] = array(
	          'product_name'  => $result['name'],
	          'sku'       => $result['sku'],
	          'quantity'    => $result['quantity'],
	          'price'     => $result['price'],
	          'total'     => $result['total'],
	          'order_ids'     => $result['order_ids'],
	          );
	        $total_item_number+=$result['quantity'];
	    }else{
	    	$childproducts = $this->getchildorderproducts($result['order_product_id']);
	    	foreach($childproducts as $val){
				$data['products'][] = array(
				  'product_name'  => $val['pdname'].'('.$result['name'].')',
		          'sku'       => $val['sku'],
		          'quantity'    => $val['quantity'],
		          'price'     => $val['price'],
		          'total'     => $val['total'],
		          'order_ids'     => $result['order_ids'].'-'.$val['order_ids'],
				);
				$total_item_number+=$result['quantity'];
			}
	    }
      }
    }
    $data['total_item_number'] = $total_item_number;

    return $data;
  }

	public function getToBillOrders($cur_month, $end_month, $billed_order_ids, $logcenter_id = 0) {
		$complete_status = $this->config->get('config_complete_status');
		$map['date_added'] = array('between',array($end_month, $cur_month));
		$map['order_status_id'] = array('in', $complete_status);
		if(!empty($billed_order_ids)) {
			$map['order_id'] = array('not in', $billed_order_ids);
		}
		$map['logcenter_id'] = array('eq', $logcenter_id);
		$query = M('order')->where($map)->select();
		return $query;
	}

	public function getToBillOrdersForExport($cur_month, $end_month, $billed_order_ids) {
		$complete_status = $this->config->get('config_complete_status');
		$map['date_added'] = array('between',array($end_month, $cur_month));
		$map['order_status_id'] = array('in', $complete_status);
		if(!empty($billed_order_ids)) {
			$map['order_id'] = array('not in', $billed_order_ids);
		}
		$query = M('order')
		->join('(select logcenter_name,logcenter_id from logcenters) l on l.logcenter_id=order.logcenter_id','left')
		->join('(select order_status_id as os_order_status_id, name as status from order_status) os on os.os_order_status_id=order.order_status_id','left')
		->where($map)->select();
		return $query;
	}

	public function getLogcenterIdsFromOrders($cur_month, $end_month, $billed_order_ids) {
    $complete_status = $this->config->get('config_complete_status');
		$map['date_added'] = array('between',array($end_month, $cur_month));
		$map['order_status_id'] = array('in', $complete_status);
		if(!empty($billed_order_ids)) {
			$map['order_id'] = array('not in', $billed_order_ids);
		}
		$query = M('order')->where($map)->group('logcenter_id')->field('logcenter_id')->select();
		return $query;
  }

	/*修改订单*/
  	public function updateOrder($order_id,$postStatus){

  		$order_model = M('order');

  		$data['id_code'] = "-".strval(rand(100,999));
		$data['payment_code'] = $postStatus['payment_code'];
		$data['payment_method'] = $postStatus['payment_method'];
		$data['shipping_telephone'] = $postStatus['shipping_telephone'];
		$data['customer_group_id'] = $postStatus['customer_group_id'];

		if($data['payment_code'] == 'cod' || $data['payment_code'] == 'magfin'){

  			$sql = "UPDATE `".DB_PREFIX."order_total` SET `value` = 0 WHERE order_id = '".$order_id."' AND code = 'favour'";
  			$this->db->query($sql);

  		}

  		$order_model->data($data)->where('order_id='.$order_id)->save();
  		return $order_id;
  	}

  		/*修改订单签收*/
  	public function signupdateOrder($order_id,$postStatus,$signtotal){

  		$order_model = M('order');

  		$data['id_code'] = "-".strval(rand(100,999));
		$data['payment_code'] = $postStatus['payment_code'];
		$data['payment_method'] = $postStatus['payment_method'];
		$data['shipping_telephone'] = $postStatus['shipping_telephone'];
		$data['customer_group_id'] = $postStatus['customer_group_id'];
		$data['sign_price'] = $signtotal;

		if($data['payment_code'] == 'cod' || $data['payment_code'] == 'magfin'){

  			$sql = "UPDATE `".DB_PREFIX."order_total` SET `value` = 0 WHERE order_id = '".$order_id."' AND code = 'favour'";
  			$this->db->query($sql);

  		}

  		$order_model->data($data)->where('order_id='.$order_id)->save();
  		// echo $order_model->getlastsql();
  		return $order_id;
  	}

  	public function updateOrderProduct($new_order_id,$order_products){

  		$order_product_model = M('order_product');
  		$order_option_model = M('order_option');

  		//拿出初始订单的信息
  		$order_product_info = $order_product_model->where('order_id='.$new_order_id)->select();

  		//编辑商品
        //* 循环降维
        $temp = array();
        foreach ($order_products as $k=>$v) {
            $temp[$v['order_product_id']] = $v;
        }
        $order_products = $temp;

        foreach ($order_product_info as $key1 => $value1) {
            $value2 = $order_products[$value1['order_product_id']];
            //* 原始代码开始
            //order_product
            $value1['order_id'] = $new_order_id ;
            $value1['quantity'] = $value2['quantity'];
            $value1['lack_quantity'] = 0;
            $sql = "SELECT product_type FROM `".DB_PREFIX."product` WHERE product_id = '".$value1['product_id']."'";
            $type_query = $this->db->query($sql);
            if($type_query->row['product_type'] != 2){
                $sql = "SELECT SUM(product_quantity) as sumquan FROM `".DB_PREFIX."stock_out_detail` WHERE out_id IN (SELECT out_id FROM `".DB_PREFIX."stock_out` WHERE refer_id = '".$new_order_id."' AND status != 0) AND product_code = '".$value1['product_code']."' AND counter_id = 0";
                $sum_query = $this->db->query($sql);
                $lack_quantity = $value1['quantity'] - $sum_query->row['sumquan'];
                $lack_quantity = $lack_quantity < 0?0:$lack_quantity;
                $value1['lack_quantity'] = $lack_quantity;
            }
            $value1['total'] = $value1['price']*$value1['quantity'];
            array_splice($value1,0,1);
            //添加
            $order_product_model->data($value1)->where('order_product_id='.$value2['order_product_id'])->save();
            //order_option
            $option_map['order_id'] = $new_order_id;
            $option_map['order_product_id'] = $value2['order_product_id'];
            $order_option_info = $order_option_model->where($option_map)->find();
            $order_option_info['order_id'] = $new_order_id;
            $order_option_info['order_product_id'] = $value2['order_product_id'];
            array_splice($order_option_info,0,1);
            //添加
            $order_option_model->data($order_option_info)->where('order_product_id='.$value2['order_product_id'])->save();
            //* 原始代码结束
        }
        // */

        /*
  		foreach ($order_product_info as $key1 => $value1) {
  			foreach ($order_products as $key2 => $value2) {
  				if($value1['order_product_id'] == $value2['order_product_id']){

  					//order_product
  					$value1['order_id'] = $new_order_id ;
  					$value1['quantity'] = $value2['quantity'];
  					$value1['lack_quantity'] = 0;
  					$sql = "SELECT product_type FROM `".DB_PREFIX."product` WHERE product_id = '".$value1['product_id']."'";
  					$type_query = $this->db->query($sql);
  					if($type_query->row['product_type'] != 2){

  						$sql = "SELECT SUM(product_quantity) as sumquan FROM `".DB_PREFIX."stock_out_detail` WHERE out_id IN (SELECT out_id FROM `".DB_PREFIX."stock_out` WHERE refer_id = '".$new_order_id."' AND status != 0) AND product_code = '".$value1['product_code']."' AND counter_id = 0";

  						$sum_query = $this->db->query($sql);

  						$lack_quantity = $value1['quantity'] - $sum_query->row['sumquan'];

  						$lack_quantity = $lack_quantity < 0?0:$lack_quantity;

  						$value1['lack_quantity'] = $lack_quantity;

  					}
  					$value1['total'] = $value1['price']*$value1['quantity'];
  					array_splice($value1,0,1);
  					//添加
					$order_product_model->data($value1)->where('order_product_id='.$value2['order_product_id'])->save();

					//order_option
  					$option_map['order_id'] = $new_order_id;
  					$option_map['order_product_id'] = $value2['order_product_id'];
  					$order_option_info = $order_option_model->where($option_map)->find();
  					$order_option_info['order_id'] = $new_order_id;
  					$order_option_info['order_product_id'] = $value2['order_product_id'];
  					array_splice($order_option_info,0,1);
  					//添加
  					$order_option_model->data($order_option_info)->where('order_product_id='.$value2['order_product_id'])->save();
  				}
  			}
  		}
        // */
  	}

  	public function updateOrderTotal($new_order_id,$new_total){

  		$order_total_model = M('order_total');
  		$order_total_info = $order_total_model->where('order_id='.$new_order_id)->select();
  		$other_total = 0;

  		foreach ($order_total_info as $key => $value) {

  			if($value['code'] == 'total'){
  				$total_value = $value;
				$total_value_id = $value['order_total_id'];
  			}

  			if($value['code'] == 'sub_total'){
  				$sub_total_value = $value;
				$sub_total_value_id = $value['order_total_id'];
  			}

  			if(($value['code'] != 'sub_total')&&($value['code'] != 'total')){
  				$order_total_model->data($value)->where('order_total_id='.$value['order_total_id'])->save();
  				$other_total+=$value['value'];
  			}
  		}
  		$order_model=M('order');
  		//拿出原始订单
  		$src_order_info = $order_model
  		->where('order_id='.$new_order_id)
  		->find();

  		$sub_total_value['value'] = $new_total;
  		$total_value['value'] = $new_total+$other_total;

  		$spreadratio = $total_value['value']/$sub_total_value['value'];

  		$spreadratio = $spreadratio == 0 || $spreadratio > 1?1:$spreadratio;

  		$sql = "SELECT op.order_product_id,p.product_type FROM `".DB_PREFIX."order_product` op LEFT JOIN `".DB_PREFIX."product` p ON p.product_id = op.product_id WHERE op.order_id = '".$new_order_id."'";

  		$order_product_query = $this->db->query($sql);

  		foreach($order_product_query->rows as $val){
  			if($val['product_type'] == 2){

  				$sql = "UPDATE `".DB_PREFIX."order_product_group` SET pay_price = price * ".$spreadratio.",pay_total = total * ".$spreadratio." WHERE order_product_id = '".$val['order_product_id']."'";

  				$this->db->query($sql);

  			}

  			$sql = "UPDATE `".DB_PREFIX."order_product` SET pay_price = price * ".$spreadratio.",pay_total = total * ".$spreadratio." WHERE order_product_id = '".$val['order_product_id']."'";

  			$this->db->query($sql);

  		}

  		$order_total_model->data($total_value)->where('order_total_id='.$total_value_id)->save();
  		$order_total_model->data($sub_total_value)->where('order_total_id='.$sub_total_value_id)->save();

  		$data['total'] = $new_total+$other_total;
  		$data['difference'] = $src_order_info['total'] - $data['total'];
  		M('order')->data($data)->where('order_id='.$new_order_id)->save();
  	}

  	public function copyOrderHistory($src_order_id,$new_order_id){
        if ($src_order_id != $new_order_id) {
            $order_history_model = M('order_history');
            $order_history_info = $order_history_model->where('order_id='.$src_order_id)->select();
            foreach ($order_history_info as $key => $value) {
                array_splice($value,0,1);
                $value['order_id'] = $new_order_id;
                $order_history_model->data($value)->add();
            }
        }
  	}
	/*修改订单*/

  	public function getOrderOperationBySku($filter){
  		$map['_string'] = "o.date_added>='".$filter['filter_date_start']."' AND o.date_added<'".$filter['filter_date_end']."'";
  		$map['p.sku'] = $filter['sku'];
  		$map['o.logcenter_id'] = $filter['filter_logcenter'];
  		//1待付款,2待发货,3待收货,5完成,7退换货,11,已退款,13拒付,16,无效,17部分发货,18全部发货,19已结束发货
		$map['o.order_status_id'] = array('in',array(1,2,3,5,11,17,18,19));

  		$order_opeartion_data = M('order_product')
  		->alias('op')
  		->join('left join `order` o ON o.order_id=op.order_id')
  		->join('left join `product` p ON p.product_id=op.product_id')
  		->join('left join `order_option` oo ON oo.order_product_id=op.order_product_id')
  		->where($map)
  		->field('op.name,op.quantity as qty,oo.name as option_name,p.packing_no,o.date_added as deliver_time,op.price as unit_price')
  		->order('oo.name,o.date_added')
  		->select();
  		return $order_opeartion_data;
  	}
  	public function addOrderHistory($new_order_id,$comment,$order_status_id){
  		$order_history_model = M('order_history');
  		$data = array(
  			'order_id'			=> $new_order_id,
  			'order_status_id'	=> $order_status_id,
  			'comment'			=> $comment,
  			'date_added'		=> date('Y-m-d H-i-s',time()),
  			);
  		$order_history_model->data($data)->add();
  	}
  	public function editProductInitiai($order_id,$flag){
  		$order_product_model = M('order_product');
	  	$map['op.order_id'] = $order_id;
	  	$order_product_data = $order_product_model
	  	->alias('op')
	  	->join('left join `order_option` oo ON oo.order_product_id=op.order_product_id')
	  	->join('left join `order` o ON o.order_id=op.order_id')
	  	->field('op.product_id,oo.product_option_value_id as option_id,o.logcenter_id,op.quantity,op.order_product_id')
	  	->where($map)
	  	->select();

	  	$product_initiai_model = M('product_initiai');
	  	foreach ($order_product_data as $key => $value) {
	  		$initiai_map['option_id'] = $value['option_id']?$value['option_id']:-1;
	  		$initiai_map['product_id'] = $value['product_id'];
	  		$initiai_map['logcenter_id'] = $value['logcenter_id'];
	  		$initiai_data = $product_initiai_model->where($initiai_map)->find();
	  		$edit_initiai_data = array();
	  		if($initiai_data){
	  			if($flag){
	  				$edit_initiai_data['real_initiai'] = $initiai_data['real_initiai'] + $value['quantity'];
	  			}else{
	  				$edit_initiai_data['real_initiai'] = $initiai_data['real_initiai'] - $value['quantity'];
	  			}
	  			$product_initiai_model->data($edit_initiai_data)->where($initiai_map)->save();
	  		}else{
	  			if($flag){
	  				$edit_initiai_data['real_initiai'] = $value['quantity'];
	  			}else{
	  				$edit_initiai_data['real_initiai'] = 0 - $value['quantity'];
	  			}
	  			$edit_initiai_data['option_id'] = $value['option_id']?$value['option_id']:-1;
	  			$edit_initiai_data['product_id'] = $value['product_id'];
	  			$edit_initiai_data['logcenter_id'] = $value['logcenter_id'];
	  			$edit_initiai_data['date_added'] = date('Y-m-d h:i:s',time());
	  			$edit_initiai_data['initial_number'] = 0 ;
	  			$product_initiai_model->data($edit_initiai_data)->add();
	  		}
	  	}
  	}

  	public function checkOrderDate($date_added,$order_status_id){//状态不等于18或5，并且下单超过5天的显示为红色
		//1待付款,2待发货,3待收货,5完成,7退换货,11,已退款,13拒付,16,无效,17部分发货,18全部发货,19已结束发货
		$order_status_id_array = array(18,5);
		$date = strtotime(date('Y-m-d',time()));
		$date_added = strtotime(date('Y-m-d',strtotime($date_added)));
		$day = ceil(($date-$date_added)/86400)+1;
		$color = '';
		if(!in_array($order_status_id, $order_status_id_array)){
			if($day<5){
				$color = 'black';
			}else{
				$color = 'red';
			}
		}
		return $color;

	}

	 public function gettradeNo($order_id){
	  $tradeNo = M('order')->where('order_id='.$order_id)->getField('yijipay_tradeNo');
	  return $tradeNo;
  }


	public function addUsertradeNo($order_id,$money){
		$model = M('order_history');
		$data['order_id'] = $order_id;
		$data['order_status_id'] = '2';
		$data['comment'] = $this->user->getUsername().'退款'.$money.'元';
		$data['date_added'] = date('Y-m-d H:i:s',time());
		$model->data($data)->add();
	}

	//过滤空格之类不可见的字符串
	public function trim_all_arr($data){
		$newdata = $data;
		foreach($newdata as $key=>$val){
			if($newdata[$key]!=null)
				$newdata[$key] = trim($val);
		}
		return $newdata;
	}

	public function getstockoutbyreferid($refer_id){
		$sql = "SELECT so.*,rt.name AS rtname,wh.name AS whname,u.username,ts.name AS tsname FROM `".DB_PREFIX."stock_out` so LEFT JOIN `".DB_PREFIX."refer_type` rt ON so.refer_type_id = rt.refer_type_id LEFT JOIN `".DB_PREFIX."warehouse` wh ON so.warehouse_id = wh.warehouse_id LEFT JOIN `".DB_PREFIX."user` u ON so.user_id = u.user_id LEFT JOIN `".DB_PREFIX."inout_state` ts ON so.status = ts.id";

		if($refer_id){
			$sql .= " WHERE refer_id = '".$refer_id."' AND so.status != 0 AND so.refer_type_id=1";

			$query = $this->db->query($sql);

			return $query->rows;
		}else
			return null;
	}

    /*
     * 获取订单中普通商品的缺货情况（订单状态“待审核【order_status_id】=20”，缺货数量【OP.lack_quantity】>0）
     * @author sonicsjh
     */
    public function getProductsWithLackQtyGTZero($availableWarehouseList, $inventoryProducts) {
        $sql = "SELECT O.`logcenter_id`,OP.`product_code`,OP.`lack_quantity`,O.`order_id` FROM `".DB_PREFIX."order_product` AS OP LEFT JOIN `".DB_PREFIX."order` AS O ON (OP.`order_id`=O.`order_id`) WHERE OP.`lack_quantity`>0 AND O.`order_status_id`='20'";
        $query = $this->db->query($sql);
        return $this->_initInventoryProducts($availableWarehouseList, $inventoryProducts, $query->rows);
    }

    /*
     * 获取订单中组合商品的缺货的情况（订单状态“待审核【order_status_id】=20”，缺货数量【OPG.lack_quantity】>0）
     * @author sonicsjh
     */
    public function getGroupProductsWithLackQtyGTZero($availableWarehouseList, $inventoryProducts) {
        return $inventoryProducts;
        $sql = "SELECT O.`logcenter_id`,OPG.`product_code`,OPG.`lack_quantity`,O.`order_id` FROM `".DB_PREFIX."order_product_group` AS OPG LEFT JOIN `".DB_PREFIX."order_product` AS OP ON (OPG.`order_product_id`=OP.`order_product_id`) LEFT JOIN `".DB_PREFIX."order` AS O ON (OP.`order_id`=O.`order_id`) WHERE OPG.`lack_quantity`>0 AND O.`order_status_id`='20'";
        $query = $this->db->query($sql);
        return $this->_initInventoryProducts($availableWarehouseList, $inventoryProducts, $query->rows);
    }

    /*
     * 公用函数，循环统计缺货数量（用于一键生成采购单）
     */
    protected function _initInventoryProducts($availableWarehouseList, $inventoryProducts, $inventoryProductsNew) {
        foreach ($inventoryProductsNew as $row) {
            $warehouseId = 10;//强制指定永康总仓，等总仓唯一性判断加上去以后动态获取 @todo
            if (array_key_exists($row['logcenter_id'], $availableWarehouseList)) {
                $warehouseId = $row['logcenter_id'];//物流中心pk和仓库pk暂时保持一致，等关联代码完善后调整 @todo
            }
            if (is_array($inventoryProducts[$warehouseId][$row['product_code']])) {
                $inventoryProducts[$warehouseId][$row['product_code']]['nums'] += $row['lack_quantity'];
                $inventoryProducts[$warehouseId][$row['product_code']]['orderIds'] .= ','.$row['order_id'];
            }else{
                $inventoryProducts[$warehouseId][$row['product_code']]['nums'] = $row['lack_quantity'];
                $inventoryProducts[$warehouseId][$row['product_code']]['orderIds'] = $row['order_id'];
            }
        }
        return $inventoryProducts;
    }

    /*
     * 获取基于商品的销售报表
     * 最近一周销售总数&总金额『 TQLW / TPLW 』
     * 最近四周销售总数&总金额『 TQLM / TPLM 』
     * 前年销售总数&总金额『 TQTY / TPTY 』
     * 毛利率（当年销售总价-总成本）/总价『 GPRTY 』
     * 当前库存总数&总成本『 SQN / SCN 』
     * 当前库存预计销售周期（当前库存/最近四周销量总数*28）【单位：天】『 SSC 』
     * 采购在途数量『 SIOW 』
     * 采购在途预计销售周期（采购在途数量/最近四周销量总数）【单位：周】『 SIOWSC 』
     * 原始表单样式由 @采销部蒋总 提供
     * @author sonicsjh
     */
    public function getGatherSaleReport($productStock) {
        //$ret = $productStock;
        $ret = array();
        foreach ($productStock as $pCode=>$info) {
            $ret[$pCode]['SQN'] = $info['availableQty'];
            $ret[$pCode]['SIOW'] = max(0, $info['qtyInOnway']);
            $ret[$pCode]['SAFE'] = max(0, $info['qtySafe']);
        }
        $maps = array(
            'LW' => date('Ymd', strtotime('-1 week')),
            'LM' => date('Ymd', strtotime('-4 week')),
            'TY' => date('Y').'0000',
            '2017' => '20170000',
        );
        foreach ($maps as $k=>$map) {
            $sql = "
            SELECT
                OP.`product_code`,SUM(OP.`quantity`) AS TQ".$k.",SUM(OP.`total`) AS TP".$k.",VP.`product_cost`
            FROM
                `".DB_PREFIX."order_product` AS OP
                LEFT JOIN `vendor` AS VP ON (VP.`vproduct_id`=OP.`product_id`)
            WHERE
                `order_id` IN (SELECT `order_id` FROM `".DB_PREFIX."order` WHERE `order_status_id`<>16 AND `order_status_id`<>1 AND `date_added`>'".$map."000000')
            GROUP BY `product_id`
            ";
            $query = $this->db->query($sql);
            foreach ($query->rows as $row) {
                $pCode = substr($row['product_code'], 0, 10);
                $ret[$pCode]['TQ'.$k] = $row['TQ'.$k];
                $ret[$pCode]['TP'.$k] = round($row['TP'.$k], 2);
                $ret[$pCode]['cost'] = $row['product_cost'];
            }
        }

        foreach ($ret as $pCode=>$info) {
            if ((int)$info['TQTY'] < 1) {
                continue;
            }
            $avgPriceThisYear = round($ret[$pCode]['TPTY']/$ret[$pCode]['TQTY'], 2);
            $ret[$pCode]['avgPriceThisYear'] = $avgPriceThisYear;
            $ret[$pCode]['GPRTY'] = (round(($avgPriceThisYear-$info['cost'])/$avgPriceThisYear, 4)*100).'%';
            //为了避免因没有销量导致的0库存成本，移到上一级函数计算
            //$ret[$pCode]['SCN'] = $info['availableQty']*$info['cost'];
            if ($info['TQLM']) {
                $ret[$pCode]['SSC'] = round($ret[$pCode]['SQN']/$info['TQLM']*28, 2);
            }else{
                $ret[$pCode]['SSC'] = '--';
            }
            if ($info['TQLM']) {
                $ret[$pCode]['SIOWSC'] = round($ret[$pCode]['SIOW']/$info['TQLM'], 2);
            }else{
                $ret[$pCode]['SIOWSC'] = '--';
            }
        }
        return $ret;
    }

    /*
     * 更新商品出库&配送比例（已废弃）
     * @author sonicsjh
     */
    public function resetRates($orderId, $ratePrefix='') {
        return true;
        if ('stock_out' != $ratePrefix && 'delivery' != $ratePrefix) {
            $ratePrefix = 'stock_out';
        }
        $data = array(
            $ratePrefix.'_rate_by_type'    => '0',
            $ratePrefix.'_rate_by_qty'     => '0',
            $ratePrefix.'_rate_by_price'   => '0',
        );
        $stockOutGatherInfo = $this->_getGatherInfoForStockOut($orderId, ($ratePrefix==='delivery'));
        if (false !== $gatherInfo) {
            $orderGatherInfo = $this->_getGatherInfoForOrder($orderId);
            $data[$ratePrefix.'_rate_by_type'] = min(100, round($stockOutGatherInfo['totalProductCode']*100/$orderGatherInfo['totalProductCode']));
            $data[$ratePrefix.'_rate_by_qty'] = min(100, round($stockOutGatherInfo['totalProductQty']*100/$orderGatherInfo['totalProductQty']));
            $data[$ratePrefix.'_rate_by_price'] = min(100, round($stockOutGatherInfo['totalProductPrice']*100/$orderGatherInfo['totalProductPrice']));
        }
        foreach ($data as $k=>$v) {
            $tmp[] = "`".$k."`='".$v."'";
        }
        $sql = "UPDATE `".DB_PREFIX."order` SET ".implode(',', $tmp)." WHERE `order_id`='".$orderId."'";
        $this->db->query($sql);
    }

    /*
     * 根据订单编号获取出库单品类数，商品总数，价格总数
     * @author sonicsjh
     */
    protected function _getGatherInfoForStockOut($orderId, $isFinish=true) {
        if ($isFinish) {
            $sql = "SELECT `product_code`,`product_quantity`,`products_money` FROM `".DB_PREFIX."stock_out_detail` WHERE `out_id` IN (SELECT `out_id` FROM `".DB_PREFIX."stock_out` WHERE `refer_id`='".$orderId."' AND `refer_type_id`='1' AND `status`=2)";
        }else{
            $sql = "SELECT `product_code`,`product_quantity`,`products_money` FROM `".DB_PREFIX."stock_out_detail` WHERE `out_id` IN (SELECT `out_id` FROM `".DB_PREFIX."stock_out` WHERE `refer_id`='".$orderId."' AND `refer_type_id`='1' AND `status`>0)";
        }
        $ret = array(
            'totalProductCode' => 0,
            'totalProductQty' => 0,
            'totalProductPrice' => 0,
        );
        $tmp = array();
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            if (false === array_key_exists($row['product_code'], $tmp)) {
                $tmp[$row['product_code']] = 1;
                $ret['totalProductCode'] += 1;
            }
            $ret['totalProductQty'] += $row['product_quantity'];
            $ret['totalProductPrice'] += $row['products_money'];
        }
        if (0 == array_sum($ret)) return false;
        return $ret;
    }

    /*
     * 根据订单编号获取订单品类数，商品总数，价格总数
     * @author sonicsjh
     */
    protected function _getGatherInfoForOrder($orderId) {
        $sql = "SELECT `product_code`,`quantity`,`pay_total` FROM `".DB_PREFIX."order_product` WHERE `order_id`='".$orderId."'";
        $ret = array(
            'totalProductCode' => 0,
            'totalProductQty' => 0,
            'totalProductPrice' => 0,
        );
        $tmp = array();
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            if (false === array_key_exists($row['product_code'], $tmp)) {
                $tmp[$row['product_code']] = 1;
                $ret['totalProductCode'] += 1;
            }
            $ret['totalProductQty'] += $row['quantity'];
            $ret['totalProductPrice'] += $row['pay_total'];
        }
        if (0 == array_sum($ret)) return false;
        return $ret;
    }

    /*
     * 获取所有用户的首单时间
     * @author sonicsjh
     */
    public function getFirstOrderDateByCustomer() {
        $ret = array();
        $sql = "SELECT `customer_id`,MIN(`date_added`) AS firstOrderDate FROM `".DB_PREFIX."order` WHERE `order_status_id`<>16 GROUP BY `customer_id`";
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $ret[$row['customer_id']] = $row['firstOrderDate'];
        }
        return $ret;
    }

    /*
     * 添加订单在线退款记录
     * @author sonicsjh
     */
    public function addOrderOnlineRefund($data) {
        $sql = "INSERT INTO `order_online_refund` SET ";
        foreach ($data as $col=>$val) {
            $sql .= "`".$col."`='".$val."',";
        }
        $result = $this->db->query(substr($sql, 0, -1));
        return $this->db->getLastId();
    }

    /*
     * 更新订单在线退款记录
     * @author sonicsjh
     */
    public function updateOrderOnlineRefund($PK, $data) {
        $sql = "UPDATE `order_online_refund` SET ";
        foreach ($data as $col=>$val) {
            $sql .= "`".$col."`='".$val."',";
        }
        $sql = substr($sql, 0, -1)." WHERE `order_online_refund_id`='".$PK."'";
        $this->db->query($sql);
    }

    /*
     * 获取单个订单的所有在线退款信息
     * @author sonicsjh
     */
    public function getOnlineRefundInfo($orderId, $displayInList=true) {
        $unApplied = 0;
        $applied = '';
        $total = 0;
        $statusList = array(
            1 => '<span style="color:blue;">处理中</span>',
            2 => '<span style="color:green;">成功</span>',
            3 => '<span style="color:red;">失败</span>',
        );
        $typeShortList = array(
            1 => '<span style="color:red;">缺</span>',
            2 => '<span style="color:green;">返</span>',
        );
        $typeList = array(
            1 => '<span style="color:red;">缺货退款</span>',
            2 => '<span style="color:green;">春节抽奖返现</span>',
        );
        //获取订单需要退款未发起申请的金额（金额超过 结束配货 后总金额 10% 的限制，或者非 易极付 支付通道）
        $sql = "SELECT `return_amount` FROM `order` WHERE `order_id`='".$orderId."'";
        $res = $this->db->query($sql);
        $unApplied = max(0, floatval($res->row['return_amount']));
        //在线退款申请记录
        $sql = "SELECT `refund_amount`,`type`,`status`,`date_added` FROM `order_online_refund` WHERE `order_id`='".$orderId."' ORDER BY `order_online_refund_id` DESC";
        $res = $this->db->query($sql);
        foreach ($res->rows as $row) {
            if (false === $displayInList) {
                $applied[] = array(
                    'dateAdded' => $row['date_added'],
                    'refundType' => $typeList[$row['type']],
                    'refundAmount' => '￥'.$row['refund_amount'],
                    'status' => $statusList[$row['status']],
                );
            }else {
                $applied .= $typeShortList[$row['type']].' ￥'.$row['refund_amount'].' ('.$statusList[$row['status']].')<br />';
            }
            //if (1 == $row['type']) {
                $total += $row['refund_amount'];
            //}
        }
        if ($total > 0){
            if (false === $displayInList) {
                $applied[] = array(
                    'dateAdded' => '',
                    'refundAmount' => '合计：￥'.$total,
                    'status' => '',
                );
            }else{
                $applied .= '合计：'.$total;
            }
        }
        return compact('unApplied', 'applied');
    }

    /*
     * 根据订单编号，获取订货人公司名称、电话，业务员电话、订单归属物流营销部，用于退款发送短信通知
     * @author sonicsjh
     */
    public function getCAndUInfo($orderId) {
        $sql = "
        SELECT
            O.`shipping_company` AS CName,O.`shipping_telephone` AS CTel,U.`contact_tel` AS UTel,O.`logcenter_id`
        FROM
            `order` AS O
            LEFT JOIN `user` AS U ON (U.`user_id`=O.`recommend_usr_id`)
        WHERE
            `order_id`='".$orderId."'
        ";
        $res = $this->db->query($sql);
        return $res->row;
    }

    /*
     * 根据财务发起的退款申请，对冲订单的未退款金额
     */
    public function clearReturnMoneyInOrder($orderId, $returnMoney) {
        $sql = "UPDATE `order` SET `return_amount`=GREATEST(0, (`return_amount`-".$returnMoney.")) WHERE `order_id`='".$orderId."'";
        $this->db->query($sql);
    }

    /*
     * 根据订单编号、抽奖活动编号，获取中奖总金额
     */
    public function getLuckDrawAmount($orderId, $luckyDrawId) {
        $sql = "SELECT LP.`product_name` FROM `luckydraw_history` AS LH LEFT JOIN `luckydraw_product` AS LP ON (LP.`luckydraw_product_id`=LH.`luckydraw_product_id`) WHERE LH.`order_id`='".$orderId."' AND LH.`luckydraw_id`='".$luckyDrawId."' AND LH.`hit`='1'";
        $res = $this->db->query($sql);
        $ret = 0;
        foreach ($res->rows as $row) {
            $ret += floatval(trim($row['product_name']));
        }
        return $ret;
    }

    /*
     * 导出所有区域已收货未收款订单列表
     */
    public function getReceiptWithOutPay() {
        $sql = "
        SELECT
            O.`order_id`,O.`shipping_fullname`,O.`shipping_company`,CONCAT(O.`shipping_country`,O.`shipping_zone`,O.`shipping_city`,O.`shipping_address`) AS `shipping_address`,O.`date_added`,O.`total` AS `o_total`,
            CONCAT(U.`username`,'（',U.`fullname`,'）') AS `user`,
            SO.`receive_date`,SO.`sale_money` AS `receive_total`
        FROM
            `order` AS O
            LEFT JOIN (SELECT `refer_id`,SUM(`sale_money`) AS sale_money,MIN(`receive_date`) AS `receive_date` FROM `stock_out` WHERE `refer_type_id`=1 and `status`=4 GROUP BY `refer_id`) AS SO ON (SO.`refer_id`=O.`order_id`)
            LEFT JOIN `user` AS U ON (U.`user_id`=O.`recommend_usr_id`)
        WHERE
            O.`order_status_id`<>16
            AND O.`order_status_id`<>13
            AND O.`payment_code`='cod'
            AND O.`is_pay`='0'
            AND O.`date_added`>'20171001000000'
            AND O.`total`>0
            AND SO.`sale_money`>0
        ";
        $res = $this->db->query($sql);
        return $res->rows;
    }
    
    public function updateOrProduct($num,$id) {
    	$sql = "UPDATE  `order_product` SET sign_quantity = ".$num." WHERE order_product_id = ".$id;
		$this->db->query($sql);
		$sql = "select *FROM `order_product` where  order_product_id = ".$id;
		$res = $this->db->query($sql);
        return $res->row;
    }
    // ($v['num'],$v['id'])
    public function updateOrProductG($num,$id) {
    	$sql = "UPDATE  `order_product_group` SET sign_quantity = ".$num." WHERE order_product_group_id = ".$id;
    	// echo $sql;
		$this->db->query($sql);
		$sql = "select *FROM `order_product_group` where  order_product_group_id = ".$id;
		$res = $this->db->query($sql);
        return $res->row;
    }

}
